/**
	AUTHOR		: GANDHI
	DATE		: 22 JUNE, 2005
	DESCRIPTION	: THIS PROC IS USED TO GET BASE PRODUCTS(PRODUCT FAMILY) AND ENDCUSTOMER REVENUE DETAILS BASED ON SEARCH CRITERIA. 

	Exec sp_revenue_by_endcustomer_report 0, '0', '3', '2005', 'schedule', '0'
*/



create         proc sp_revenue_by_endcustomer_report

@product_Id int,
@end_customer varchar(65),
@quarter_input varchar(10),
@year_input varchar(10),
@category varchar(15),
@defaultPrompt varchar(50)

as
begin

	--declare @finished_part_Number varchar(50)
	declare @part_Number varchar(50)
	declare @schedule_Date datetime
	declare @amount float
	declare @status varchar(50)
	declare @customer_Name varchar(65)
	declare @month varchar(10)
	declare @sales_rep varchar(50)
	declare @end_user varchar(65)

	declare @LineItem_ID int
	declare @Unit_Price float	
	declare @Shipped_Qty int
			
	declare @revised_date datetime 
	declare @requested_date datetime 

	declare @req_date datetime
	declare @date_From varchar(10)
	declare @date_To varchar(10)
	declare @dateDiff datetime
	declare @hold_amount float
	declare @backlog_amount float
	declare @shipped_amount float
	declare @rma_hold_amount float
	declare @rma_shipped_amount float
	DECLARE @TYPE VARCHAR(20)
	
	declare @base_product_name varchar(50)
	--declare @process_stepid bigint
	--declare @product_type varchar(50)
	--declare @product_type_order varchar(50)
	--declare @finished_product_Id int
	declare @baseProductId int

	declare @quantity  int
	declare @price float

	DECLARE @TEMP_CSLIT_ID INT
	DECLARE @TEMP_SHIPPED_PART_NO VARCHAR(200)
	DECLARE @TEMP_PRODUCT_ID INT
	/*DECLARE @CHK_PRODUCT_FOR_BOARD_OR_KIT VARCHAR(50)
	DECLARE @CHK_PRODUCT_FOR_BOARD_KIT_OR_CHIP VARCHAR(50)

	DECLARE @LOT_ID INT	
	DECLARE @LOT_PRODUCT_ID INT
	DECLARE @LOT_PARENT_PRODUCT_ID INT
	DECLARE @PRODUCT_PARENT_PRODUCT_ID INT
	DECLARE @LINEITEM_PRODUCT_PARENT_PRODUCT_ID INT
	DECLARE @LOT_SHIPPED_DATE DATETIME
	DECLARE @LOT_SHIPPED_QTY BIGINT
	DECLARE @LOT_BASEPRODUCTNAME VARCHAR(50)
	DECLARE @LOT_BASE_PRODUCT_ID VARCHAR(20)
	DECLARE @LOT_PROCESS_STEP_ID INT
	DECLARE @TEMP_LOT_PRODUCT_ID INT
	DECLARE @LOT_PRODUCT VARCHAR(50)
	DECLARE @LOT_PS_TYPE  VARCHAR(50)
	DECLARE @LOT_PRODUCT_TYPE  VARCHAR(50)
	DECLARE @LOT_PRODUCT_TYPE_ID INT
	DECLARE @LINEITEM_PRODUCT_ID INT	
	DECLARE @TEMP_UNIT_COST FLOAT	*/

	declare @results Table( BaseProductName varchar(50),BaseProductId int,ProductId int,PartNumber varchar(50),ScheduleDate datetime, Amount float,Status varchar(15),Type varchar(5), Quantity int, Price float, EndCustomer varchar(65))
	declare @final_results Table( BaseProductName varchar(50),BaseProductID int,ProductId int,PartNumber varchar(50),RequestedScheduleDate datetime, Amount float,Status varchar(15),Type varchar(5), Quantity int, Price float, Month int, QtStart int, QtEnd int, Year int, EndCustomer varchar(65))
	declare @temp_results Table ( ProductId int,Amount float,Status varchar(15),Type varchar(5), Quantity int, Price float, Month int, QtStart int, QtEnd int, Year int, EndCustomer varchar(65))
	declare @final_results_table Table (EndCustomer varchar(65),Hold_Amount float, Open_Amount float, Shipped_Amount float, TYPE VARCHAR(20))
	declare @end_customer_table Table (EndCustomer varchar(65))
		
	declare @CoolCursor CURSOR
	declare @CsolCursor CURSOR
	declare @RmaItemCursor cursor
	declare @FinalResultsCursor CURSOR
	declare @EndCustomerCursor CURSOR
	

		-- For Shipped Details	
		SET @CsolCursor = CURSOR for
		select P.Part_Number, C.C_Name,
		CST.CST_Ship_Date, (COOL.COOL_Unit_Cost*CSLIT.CSLIT_Qty_Shipped) as Amount, CSLIT_Status, COOL_Requested_Date, SO.SO_Sales_Person, 
                CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID = -1 OR SO.SO_ODM_ID = 0 OR SO.SO_ODM_ID = '' ) THEN C.C_Name
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
			END AS END_USER, CSLIT.CSLIT_Qty_Shipped, COOL.COOL_Unit_Cost, CSLIT.CSLIT_ID, ISNULL(CSLIT.CSLIT_Shipped_Part_Number, ''), P.Product_ID, 	
		(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = CASE 
				WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID
				WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID
			END ) 
		AS BASEPRODUCTNAME,	
		CASE 
				WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID
				WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID
		END  AS BASEPRODUCTID	
		from Customer_Open_Order_LineItem COOL, 
		     customer_shipping_line_item_table CSLIT,
		     Product P, Sales_Order SO, Customer C ,
		     Customer_Shipping_Table CST

		where
		CST.CustomerShipping_ID = CSLIT.CustomerShipping_ID
		and CSLIT.LineItem_ID = COOL.LineItem_ID
		--and COOL.Product_ID = @finished_product_Id
                and COOL.Product_ID = P.Product_ID
		and COOL.SalesOrder_ID = SO.SalesOrder_ID
		and SO.SO_Is_Sample = 1
                and SO.Customer_ID = C.Customer_ID 
		and CST.CST_STATUS = 'SHIPPED' 
		AND CONVERT(VARCHAR,DATEPART(YYYY,CST_Ship_Date)) = @YEAR_INPUT
		and so_is_consignment = 1

		-- For Backlog Details		
		if (@category = 'REQUESTED')                
        	begin
			SET @CoolCursor = CURSOR for
			select P.Part_Number, customer.C_Name,
			COOL.COOL_Requested_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Person, 
                        CASE WHEN ( SALES_ORDER.SO_ODM_ID IS NULL OR SALES_ORDER.SO_ODM_ID = -1  OR SALES_ORDER.SO_ODM_ID = 0 OR SALES_ORDER.SO_ODM_ID = '' ) THEN customer.C_Name
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SALES_ORDER.SO_ODM_ID )
			END AS END_USER, cool_qty, cool_unit_cost, P.Product_ID, 	
			(SELECT PART_NUMBER FROM PRODUCT  WHERE PRODUCT_ID = CASE 
					WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID
					WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID
				END ) 
			AS BASEPRODUCTNAME,	
			CASE 
					WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID
					WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID
			END  AS BASEPRODUCTID				
			from  
			     Customer_Open_Order_LineItem COOL, 
			     Sales_Order,Customer,
			     Product P	 

			where 
			COOL.Product_ID=P.Product_ID 
			and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
			and Sales_Order.Customer_ID=customer.Customer_ID
			and Sales_Order.SO_Is_Sample = 1
	                and COOL_Status <> 'SHIPPED'
			--and COOL.Product_ID= @finished_product_Id
		end
		else if (@category = 'SCHEDULE')
		begin
			SET @CoolCursor = CURSOR for
			select P.Part_Number,
	        	customer.C_Name,COOL.COOL_Schedule_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Person, 
                        CASE WHEN ( SALES_ORDER.SO_ODM_ID IS NULL OR SALES_ORDER.SO_ODM_ID = -1  OR SALES_ORDER.SO_ODM_ID = 0 OR SALES_ORDER.SO_ODM_ID = '' ) THEN customer.C_Name
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SALES_ORDER.SO_ODM_ID )
			END AS END_USER, cool_qty, cool_unit_cost, P.Product_ID, 	
			(SELECT PART_NUMBER FROM PRODUCT  WHERE PRODUCT_ID = CASE 
					WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID
					WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID
				END ) 
			AS BASEPRODUCTNAME,	
			CASE 
					WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID
					WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID
			END  AS BASEPRODUCTID					
			from  
			     Customer_Open_Order_LineItem COOL, 
			     Sales_Order,Customer, 
			     product P	
			where 
			COOL.Product_ID=P.Product_ID 
			and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
			and Sales_Order.Customer_ID=customer.Customer_ID
			and Sales_Order.SO_Is_Sample = 1
	                and COOL_Status <> 'SHIPPED'
			--and COOL.Product_ID= @finished_product_Id
			and (cool.cool_status = 'approved'  or  cool.cool_status = 'hold')
		end


		-- For RMA details
		SET @RMAITEMCURSOR = CURSOR FOR
		SELECT CUSTOMER_NAME , RLIT.PART_NUMBER , RMA_STATUS , RMA_REQUESTED_DATE , RT.CLOSE_DATE , QUANTITY , UNIT_PRICE, IS_CREDIT , RLIT.STATUS , RLIT.CLOSE_DATE,
		CASE WHEN ( RT.END_USER_ID IS NULL OR RT.END_USER_ID = '' OR RT.END_USER_ID = -1 ) THEN RT.CUSTOMER_NAME
		ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = RT.END_USER_ID )
		END AS END_USER,
		P.Product_ID, 	
		(SELECT PART_NUMBER FROM PRODUCT  WHERE PRODUCT_ID = CASE 
				WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID
				WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID
		END ) 
		AS BASEPRODUCTNAME,	
		CASE 
				WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID
				WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID
		END AS BASEPRODUCTID			
		FROM RMA_TABLE RT, RMA_LINE_ITEM_TABLE RLIT, CUSTOMER CUST, PRODUCT P, PROCESS_STEP PS
		WHERE 	
			RT.RMA_ID = RLIT.RMA_ID 
			AND RMA_STATUS <> 'CANCELLED'
			AND RT.CUSTOMER_NAME = CUST.C_NAME 
			AND RLIT.PART_NUMBER = P.PART_NUMBER
			AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
			AND PS.PS_TYPE_NAME = 'FINISHED GOODS'
		
					
		-- SHIPPED DETAILS CURSOR
		open @CsolCursor 
		fetch next from @CsolCursor
		into @part_Number, @customer_Name,@schedule_Date,@amount,@status, @requested_date, @sales_rep, @end_user, @quantity, @price, @TEMP_CSLIT_ID, @TEMP_SHIPPED_PART_NO, @TEMP_PRODUCT_ID, @base_product_name, @baseProductId 
		while @@fetch_status = 0
		begin
			
			if(@amount is null)
			begin
				set @amount = 0
			end
		
			if(@schedule_Date is null OR @schedule_Date= '')
			begin
				set @schedule_Date = @requested_date
			end
		
			insert into @results(BaseProductName,BaseProductId,ProductId,PartNumber,ScheduleDate,Amount,Status,Type, Quantity, Price, EndCustomer) values(@base_product_name,@baseProductId,@TEMP_PRODUCT_ID,@part_Number,@schedule_Date, @amount, @status, 'COOL', @quantity, @price, @end_user) 	

			set @part_number = null
			set @schedule_date = null
			set @amount = null
			set @status = null
			set @requested_date = null
			set @quantity = null
			set @price = null
			set @customer_Name = null
			set @sales_rep = null
			set @end_user = null		
			set @TEMP_PRODUCT_ID = null
			set @base_product_name = null 
			set @baseProductId = null
			
			fetch next from @CsolCursor into @part_Number, @customer_Name,@schedule_Date,@amount,@status, @requested_date, @sales_rep, @end_user, @quantity, @price, @TEMP_CSLIT_ID, @TEMP_SHIPPED_PART_NO, @TEMP_PRODUCT_ID, @base_product_name, @baseProductId 
		end
		close @CsolCursor
		deallocate @CsolCursor
		
				
		-- BACKLOG DETAILS CURSOR
		set @part_number = null
		set @customer_name = null
		set @schedule_date = null
		set @amount = null
		set @status = null
		set @LineItem_ID = null
		set @unit_price = null
		set @requested_date = null
		set @revised_date = null
		set @quantity = null
		set @price = null
		set @sales_rep = null
		set @end_user = null	
		set @TEMP_PRODUCT_ID = null
		set @base_product_name = null 
		set @baseProductId = null
		open @CoolCursor
		fetch next from @CoolCursor
		into @part_Number, @customer_Name,@schedule_Date,@amount, @status , @LineItem_ID , @Unit_Price, @revised_date, @sales_rep, @end_user, @quantity, @price, @TEMP_PRODUCT_ID, @base_product_name, @baseProductId 
				
		while @@fetch_status = 0
		begin
			if(@amount is null)
			begin
				set @amount = 0
			end
			if (@category = 'REQUESTED')                
		        begin
				if( @schedule_Date < CONVERT(datetime,CURRENT_TIMESTAMP) )
				begin
					set @schedule_Date = CONVERT(datetime,CURRENT_TIMESTAMP)
				end
				else
				begin
					set @schedule_Date = @schedule_Date
				end
			end	
			
			if (@category = 'SCHEDULE')
			begin
				if(@schedule_Date is null OR @schedule_Date= '')
				begin
					set @schedule_Date = CONVERT(datetime,CURRENT_TIMESTAMP)
				end
				else
				begin
					set @schedule_Date = @schedule_Date
					if( @schedule_Date < CONVERT(datetime,CURRENT_TIMESTAMP) )
					begin
						set @schedule_Date = CONVERT(datetime,CURRENT_TIMESTAMP)
					end
					else
					begin
						set @schedule_Date = @schedule_Date
					end
				end
			end
					
			if (@category = 'SCHEDULE')
			begin
				if(@revised_date is not null OR @revised_date <> '')
				begin
					set @schedule_Date = @revised_date
						
					if( @schedule_Date < CONVERT(datetime,CURRENT_TIMESTAMP) )
					begin
						set @schedule_Date = CONVERT(datetime,CURRENT_TIMESTAMP)
					end
					else
					begin
						set @schedule_Date = @schedule_Date
					end
				end
			end
		
			select @Shipped_Qty = isNull(sum(CSLIT_Qty_Shipped),0) from Customer_Shipping_Line_Item_Table where LineItem_ID = @LineItem_ID and CSLIT_Status = 'SHIPPED'
					
			set @amount = @amount - (@Unit_Price * @Shipped_Qty)	
			if ( @amount < 0 )
			begin
				set @amount = 0	
			end
				
			set @quantity = @quantity - @shipped_qty
			
			if ( @quantity  < 0)
			begin
				set @quantity = 0
			end
		
			insert into @results(BaseProductName,BaseProductId,ProductId,PartNumber,ScheduleDate,Amount,Status,Type, Quantity, Price, EndCustomer) values(@base_product_name,@baseProductId,@TEMP_PRODUCT_ID,@part_Number,@schedule_Date, @amount ,@status, 'COOL', @quantity, @price, @end_user ) 	
		
			set @part_number = null
			set @customer_name = null
			set @schedule_date = null
			set @amount = null
			set @status = null
			set @LineItem_ID = null
			set @unit_price = null
			set @requested_date = null
			set @revised_date = null
			set @quantity = null
			set @price = null
			set @sales_rep = null
			set @end_user = null	
			set @TEMP_PRODUCT_ID = null
			set @base_product_name = null 
			set @baseProductId = null

			fetch next from @CoolCursor into @part_Number, @customer_Name,@schedule_Date,@amount, @status , @LineItem_ID , @Unit_Price, @revised_date, @sales_rep, @end_user, @quantity, @price, @TEMP_PRODUCT_ID, @base_product_name, @baseProductId 

		end
		close @CoolCursor
		deallocate @CoolCursor
		

	-- RMA DETAILS CURSOR
	
	DECLARE @LINEITEMSTATUS VARCHAR(15)
	DECLARE @LINEITEMCLOSEDATE DATETIME	
	DECLARE @RMA_REQUESTED_DATE DATETIME
	DECLARE @RMA_CLOSE_DATE     DATETIME
	DECLARE @IS_CREDIT INT
	SET @IS_CREDIT = 1
	DECLARE @RMAENDUSER VARCHAR(50)		

	SET @CUSTOMER_NAME = NULL
	SET @PART_NUMBER = NULL
	SET @STATUS = NULL
	SET @RMA_REQUESTED_DATE = NULL
	SET @RMA_CLOSE_DATE = NULL
	SET @QUANTITY = NULL
	SET @PRICE = NULL
	SET @IS_CREDIT = NULL
	SET @LINEITEMSTATUS = NULL
	SET @LINEITEMCLOSEDATE = NULL
	SET @RMAENDUSER = NULL
	SET @TEMP_PRODUCT_ID = NULL
	SET @BASE_PRODUCT_NAME = NULL 
	SET @BASEPRODUCTID = NULL

	OPEN @RMAITEMCURSOR
	FETCH NEXT FROM @RMAITEMCURSOR INTO @CUSTOMER_NAME , @PART_NUMBER , @STATUS , @RMA_REQUESTED_DATE, @RMA_CLOSE_DATE , @QUANTITY , @PRICE, @IS_CREDIT , @LINEITEMSTATUS , @LINEITEMCLOSEDATE, @RMAENDUSER, @TEMP_PRODUCT_ID, @BASE_PRODUCT_NAME, @BASEPRODUCTID 
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF ( @LINEITEMSTATUS = 'CLOSE' )
		BEGIN
			SET @STATUS = 'CLOSE'
			SET @RMA_CLOSE_DATE = @LINEITEMCLOSEDATE
		END


		IF (@STATUS = 'ISSUED')
		BEGIN
			SET @SCHEDULE_DATE = CONVERT(DATETIME,CURRENT_TIMESTAMP)
			SET @STATUS = 'APPROVED'
		END
		ELSE IF (@STATUS = 'CLOSE')
		BEGIN
			SET @SCHEDULE_DATE = @RMA_CLOSE_DATE
			SET @STATUS = 'SHIPPED'
		END

		IF( @IS_CREDIT = 1)
		BEGIN
			SET @AMOUNT = 0
		END
		ELSE IF (@IS_CREDIT = 0)
		BEGIN
			SET @AMOUNT = @QUANTITY * @PRICE 
		END

		INSERT INTO @RESULTS(BASEPRODUCTNAME,BASEPRODUCTID,PRODUCTID,PARTNUMBER,SCHEDULEDATE,AMOUNT,STATUS,TYPE, QUANTITY, PRICE, ENDCUSTOMER) VALUES( @BASE_PRODUCT_NAME, @BASEPRODUCTID, @TEMP_PRODUCT_ID, @PART_NUMBER,@SCHEDULE_DATE, @AMOUNT,@STATUS,'RMA', @QUANTITY , @PRICE, @RMAENDUSER ) 		
		
		SET @AMOUNT = 0
		SET @IS_CREDIT = 1
		
		SET @CUSTOMER_NAME = NULL
		SET @PART_NUMBER = NULL
		SET @STATUS = NULL
		SET @RMA_REQUESTED_DATE = NULL
		SET @RMA_CLOSE_DATE = NULL
		SET @QUANTITY = NULL
		SET @PRICE = NULL
		SET @IS_CREDIT = NULL
		SET @LINEITEMSTATUS = NULL
		SET @LINEITEMCLOSEDATE = NULL
		SET @RMAENDUSER = NULL

		FETCH NEXT FROM @RMAITEMCURSOR INTO @CUSTOMER_NAME , @PART_NUMBER , @STATUS , @RMA_REQUESTED_DATE, @RMA_CLOSE_DATE , @QUANTITY , @PRICE, @IS_CREDIT , @LINEITEMSTATUS , @LINEITEMCLOSEDATE, @RMAENDUSER, @TEMP_PRODUCT_ID, @BASE_PRODUCT_NAME, @BASEPRODUCTID
	END	
		
	CLOSE @RMAITEMCURSOR
	DEALLOCATE @RMAITEMCURSOR
		
 	
	declare @FinalCursor CURSOR

	declare @month_number int
	declare @quarter_start int
	declare @quarter_end int
	declare @act_year int

	declare @baseProductName varchar(50)
	declare @bPId int
	declare @pId int
	declare @pTypeOrder int
	declare @pType varchar(50)
	declare @pNumber varchar(50)
	declare @cName varchar(50)
	declare @revDate datetime
	declare @revAmount float
	declare @revStatus varchar(15)
	declare @revType varchar(5)
	declare @sRep varchar(50)
	declare @eUser varchar(50)
	declare @revQuantity int
	declare @revUnitPrice float
	
			
	if( @quarter_input <> 'All')
	begin
		set @FinalCursor = cursor for 
		select * from @results WHERE DATEPART(Q, ScheduleDate ) = @quarter_input and Year(ScheduleDate) = @year_input
	end
	else
	begin
		set @FinalCursor = cursor for 
		select * from @results where Year(ScheduleDate) = @year_input
	end
	open @FinalCursor 
		fetch next from @FinalCursor
		into @baseProductName, @bPId, @pId, @pNumber, @revDate, @revAmount, @revStatus, @revType, @revQuantity, @revUnitPrice, @eUser
		while @@fetch_status = 0
		begin

 			select @act_year = DATEPART(yyyy, @revDate) 

 			select @month_number = DATEPART(mm, @revDate) 
 			
 			if (@month_number >=1 and @month_number <=3)
 			begin
 				set @quarter_start = 1
 				set @quarter_end =3
 				
 			end
 			else if (@month_number >=4 and @month_number <=6)
 			begin
 				set @quarter_start = 2
 				set @quarter_end =6
		
 			end
 			else if (@month_number >=7 and @month_number <=9)
 			begin
 				set @quarter_start = 3
 				set @quarter_end =9
  				
 			end						
 			else if (@month_number >=10 and @month_number <=12)
 			begin
 				set @quarter_start = 4
 				set @quarter_end =12
  			end

			insert into @final_results values( @baseProductName, @bPId, @pId, @pNumber, @revDate, @revAmount, @revStatus, @revType, @revQuantity, @revUnitPrice, @month_number, @quarter_start, @quarter_end, @act_year, @eUser )
				set @baseProductName = null
				set @bPId = null
				set @pId = null
				set @pNumber = null
				set @revDate = null
				set @revAmount = null
				set @revStatus = null
				set @revType = null
				set @revQuantity = null
				set @revUnitPrice = null
				set @month_number = null
				set @quarter_start = null
				set @quarter_end = null
				set @act_year = null
				set @eUser = null					

			fetch next from @FinalCursor
			into @baseProductName, @bPId, @pId, @pNumber, @revDate, @revAmount, @revStatus, @revType, @revQuantity, @revUnitPrice, @eUser
	
		end

	close @FinalCursor
	deallocate @FinalCursor

	if (@product_Id = 0 )
	begin
		insert into @temp_results select  BaseProductId, sum(Amount) as Amount, Status, Type, sum(Quantity) as Quantity, sum(Price) as Price, Month, QtStart, QtEnd, Year, EndCustomer from @final_results 
		group by EndCustomer,BaseProductId,Month, QtStart, QtEnd,Year,Status,Type 
	end
	else
	begin
		insert into @temp_results select  BaseProductId, sum(Amount) as Amount, Status, Type, sum(Quantity) as Quantity, sum(Price) as Price, Month, QtStart, QtEnd, Year, EndCustomer from @final_results where BaseProductId = @product_Id
		group by EndCustomer,BaseProductId,Month, QtStart, QtEnd,Year,Status,Type 
	end

	
	if (@end_customer = '0' )
	begin
		insert into @end_customer_table select distinct EndCustomer from @temp_results
	end
	else
	begin
		insert into @end_customer_table select distinct EndCustomer from @temp_results where EndCustomer = @end_customer
	end

	declare @EndCustomer_Initial varchar(50)
	
	set @EndCustomerCursor = cursor for select * from @end_customer_table
	open @EndCustomerCursor 
		fetch next from @EndCustomerCursor
		into  @EndCustomer_Initial
		while @@fetch_status = 0
		begin
			--@hold_amount @backlog_amount  @shipped_amount 
			
			select @hold_amount = sum(isnull(Amount,0)), @TYPE = TYPE from @temp_results where EndCustomer = @EndCustomer_Initial and Type = 'COOL' and Status = 'HOLD' GROUP BY TYPE
			select @backlog_amount = sum(isnull(Amount,0)), @TYPE = TYPE from @temp_results where EndCustomer = @EndCustomer_Initial and Type = 'COOL' and Status = 'APPROVED' GROUP BY TYPE
			select @shipped_amount = sum(isnull(Amount,0)), @TYPE = TYPE from @temp_results where EndCustomer = @EndCustomer_Initial and Type = 'COOL' and Status = 'SHIPPED'  GROUP BY TYPE
			IF @TYPE = 'COOL'
			BEGIN
				insert into @final_results_table (EndCustomer ,Hold_Amount , Open_Amount , Shipped_Amount,TYPE)
				values (@EndCustomer_Initial,@hold_amount,@backlog_amount,@shipped_amount,@TYPE )
			END
			SET @TYPE = NULL
			select @rma_hold_amount = sum(isnull(Amount,0)), @TYPE = TYPE from @temp_results where EndCustomer = @EndCustomer_Initial and Type = 'RMA' and Status = 'APPROVED' GROUP BY TYPE
			select @rma_shipped_amount = sum(isnull(Amount,0)), @TYPE = TYPE from @temp_results where EndCustomer = @EndCustomer_Initial and Type = 'RMA' and Status = 'SHIPPED' GROUP BY TYPE			
			IF @TYPE = 'RMA'
			BEGIN
				insert into @final_results_table (EndCustomer ,Hold_Amount , Open_Amount , Shipped_Amount,TYPE)
				values (@EndCustomer_Initial,@rma_hold_amount,NULL,@rma_shipped_amount,@TYPE )
			END

			set @hold_amount = 0
			set @backlog_amount = 0
			set @shipped_amount = 0 
			set @rma_hold_amount = 0
			set @rma_shipped_amount = 0
			set @EndCustomer_Initial = null
			SET @TYPE = NULL

			fetch next from @EndCustomerCursor
			into  @EndCustomer_Initial
		end
		close @EndCustomerCursor
		deallocate @EndCustomerCursor

	/*if @end_customer = '0' and @product_Id = '0'
	begin
		select * from @final_results order by EndCustomer
	end
	else if @end_customer <> '0' and @product_Id = '0'
	begin
		select * from @final_results where EndCustomer = @end_customer order by EndCustomer
	end
	else if @end_customer = '0' and @product_Id <> '0'
	begin
		select * from @final_results where BaseProductID = @product_Id order by EndCustomer
	end
	else if @end_customer <> '0' and @product_Id <> '0'
	begin
		select * from @final_results where BaseProductID = @product_Id and  EndCustomer = @end_customer order by EndCustomer
	end
	else
	begin
		select * from @final_results order by EndCustomer
	end*/

	select * from @final_results_table order by EndCustomer
end